import traceback
from urllib.parse import quote_plus

import numpy as np
import pandas as pd
import pymysql

# from dbs import mysqlconn
import streamlit as st
from numpy import float64
from sqlalchemy import create_engine


def get_price_gplus_store(erchengfulfilltype,country):
    conn_mysql = pymysql.connect(host='124.71.174.53',
                                 user='developer',
                                 password='csbd@123',
                                 database='csbd')
    df_price_gplus = pd.read_sql(sql='''    
            select storerela.store_country 站点,commo.country,commo.erp_sku,commo.配送渠道,commo.预计定价
            from
            (
                select 
                distinct 站点,substring_index(站点,':',-1) country,erp_sku,
                case when 配送渠道='买家自配送' then 'fbm' when 配送渠道='亚马逊配送' then 'fba' else '' end 配送渠道,
                (case when 优惠价=0 or 优惠价 is null then 原价 else 优惠价 end) 预计定价
                from
                gplus_commodity_management
                where id in (select min(id) id from gplus_commodity_management group by 站点,erp_sku,substring_index(站点,':',-1))
    		) commo
    		left join
    		(
    		    select concat(concat(store,':'),country) store,store_country from gplus_store_relate
    		) storerela
				    		on commo.站点=storerela.store

        ''',con=conn_mysql)
    df_price_gplus=df_price_gplus.loc[df_price_gplus['配送渠道']==erchengfulfilltype]
    df_price_gplus=df_price_gplus.loc[df_price_gplus['country']==country]
    df_price_gplus['预计定价']=df_price_gplus['预计定价'].astype('float64')
    df_price_gplus.drop(columns=['配送渠道'],inplace=True)
    df_price_gplus.drop(columns=['country'],inplace=True)
    df_price_gplus=df_price_gplus.groupby(['站点','erp_sku']).sum().reset_index()
    return df_price_gplus

print(get_price_gplus_store('fba','CA'))